/*
 * Funambol is a mobile platform developed by Funambol, Inc. 
 * Copyright (C) 2008 Funambol, Inc.
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU Affero General Public License version 3 as published by
 * the Free Software Foundation with the addition of the following permission 
 * added to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED
 * WORK IN WHICH THE COPYRIGHT IS OWNED BY FUNAMBOL, FUNAMBOL DISCLAIMS THE 
 * WARRANTY OF NON INFRINGEMENT  OF THIRD PARTY RIGHTS.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU Affero General Public License 
 * along with this program; if not, see http://www.gnu.org/licenses or write to
 * the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 * MA 02110-1301 USA.
 * 
 * You can contact Funambol, Inc. headquarters at 643 Bair Island Road, Suite 
 * 305, Redwood City, CA 94063, USA, or at email address info@funambol.com.
 * 
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU Affero General Public License version 3.
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License
 * version 3, these Appropriate Legal Notices must retain the display of the
 * "Powered by Funambol" logo. If the display of the logo is not reasonably 
 * feasible for technical reasons, the Appropriate Legal Notices must display
 * the words "Powered by Funambol".
 */

#include "EventDataStore.h"

static bool convertStringToBool(const char* d) {
    return strcmp(d, "0") == 0 ? false : true;
}

static const char* getElementOfArray(const ArrayList& al, int index) {        
    StringBuffer* ret = (StringBuffer*)al.get(index);
    LOG.debug("getElementOfArray = %s", ret->c_str());   
    return ret->c_str();    
}

static long getIntElementOfArray(const ArrayList& al, int index) {
    StringBuffer* ret = (StringBuffer*)al.get(index);
    LOG.debug("getIntElementOfArray = %s", ret->c_str());
    long result = atol(ret->c_str());
    return result;    
    
}

/**
 * It escapes the query for the characters that are not permitted
 */
void escapesQuery(StringBuffer& query) {
    query.replaceAll("'", "''");
    return;
}

/**
 * Make a select on the fun_event_state to understand if it exists. If the query fails,
 * the table doesn't exit so the db is not initialized by the client.
 * In this case, the plug-in creates the table and the needed triggers
 * @return 0 if ok, a value if error.
 */
int EventDataStore::checkDBFunTable() {

    StringBuffer query("SELECT count(*) from fun_event_state;");
    ArrayList al;
    int ret = 0;
    int numrow = 0;
    ret = driver.queryIndex(query, al);
    if (al.size() == 0) {
        ret = driver.execute(CREATE_FUN_EVENT_STATE);
        if (ret != 0) {
            LOG.error("Fatal error: cannot create the fun_event_state table");
            ret = 1;
            goto finally;
        }                         
        // create the triggers
        driver.commit();
        ret = driver.execute(TRIGGER_EVENT_NEW);
        if (ret != 0) {
            ret = 2;
            LOG.error("Fatal error: cannot create the trigger for new event on fun_event_state table");
            goto finally;
        }
        driver.commit();
        ret = driver.execute(TRIGGER_EVENT_UPDATE);
        if (ret != 0) {
            ret = 3;
            LOG.error("Fatal error: cannot create the trigger for updated event on fun_event_state table");
            goto finally;
        }
        driver.commit();
        ret = driver.execute(TRIGGER_EVENT_DELETE);
        if (ret != 0) {
            ret = 4;
            LOG.error("Fatal error: cannot create the trigger for deleted event on fun_event_state table");
            goto finally;
        }
        driver.commit();
        
        // triggers on Alarm table
        ret = driver.execute(TRIGGER_ALARM_UPDATE);
        if (ret != 0) {
            ret = 5;
            LOG.error("Fatal error: cannot create the trigger for updated alarm event on fun_event_state table");
            goto finally;
        }
        driver.commit();
        ret = driver.execute(TRIGGER_ALARM_DELETE);
        if (ret != 0) {
            ret = 6;
            LOG.error("Fatal error: cannot create the trigger for deleted alarm event on fun_event_state table");
            goto finally;
        }
        driver.commit();
        
        // triggers on Recurrence table
        ret = driver.execute(TRIGGER_RECURRENCE_UPDATE);
        if (ret != 0) {
            ret = 7;
            LOG.error("Fatal error: cannot create the trigger for updated Recurrence event on fun_event_state table");
            goto finally;
        }
        driver.commit();
        
        // initialize the fun_event_state table with the values of the event that are already present
        // into the db
        al.clear();
        query = EVENT_ALL_ROW;
        ret = driver.queryIndex(query, al);
        for (int i = 0; i < al.size(); i++) {
            // for every previous event the conventional date of insert is 100
            query.sprintf("insert into fun_event_state(event_id, creation, updated, deleted, updated_from_client, sent_status) VALUES \
                          (%s, 100, 0, 0, 0, 0);", ((StringBuffer*)(al.get(i)))->c_str());
            LOG.debug("Init fun_event_table with event: query %s", query.c_str());
            ret = driver.execute(query.c_str());
            if (ret != 0) {
                ret = 8;
                LOG.error("Fatal error: cannot initialize the fun_event_state table with previous appointment");
            }
            driver.commit();
        }    
          
    }
    al.clear();
    // check if the Calendar table has been ever initialized
    ret = driver.queryIndex(CALENDAR_NUM_ROW, al);
    
    if (al.size() >= 1) {
        StringBuffer* el = (StringBuffer*)al.get(0);
        numrow = atoi(el->c_str());
    }
    
    if (numrow <= 1) {
        LOG.debug("The Calendar and CalendarChanges tables were never used: initialization in progress...");
        ret = driver.execute(INITIALIZE_CALENDAR_TABLE);
        if (ret != 0) {
            ret = 9;
            LOG.error("Fatal error: cannot initialize the Calendar table");
            goto finally;
        }
        ret = driver.execute(INITIALIZE_CALENDAR_CHANGES_TABLE1);
        if (ret != 0) {
            ret = 10;
            LOG.error("Fatal error: cannot initialize the CalendarChanges table");
            goto finally;
        }
        ret = driver.execute(INITIALIZE_CALENDAR_CHANGES_TABLE2);
        if (ret != 0) {
            ret = 11;
            LOG.error("Fatal error: cannot initialize the CalendarChanges table");
            goto finally;
        }
    }
    
finally:   
    // driver.disconnect();     
    return ret;

}

/** Creates a StringBuffer that is the format that must be used 
*   inside the Event table
*/
StringBuffer EventDataStore::getDate(Date& d) {    
    StringBuffer ret = convertStandardToAppleDate(d);
    LOG.debug("EventDataStore: getDate %s", ret.c_str());
    return ret;
}

StringBuffer getLocalMidnight(Date& d) {
    StringBuffer ret = convertStandardToAppleDate(d, true);
    LOG.debug("EventDataStore: getLocalMidnight %s", ret.c_str());
    return ret;
    
}

/**
* Creates a StringBuffer that is the format that must be used 
* inside the Event table. The name can be retieved using the info
* stored inside the Timezone object
*/
StringBuffer EventDataStore::getTimezoneName(Timezone& tz) {    
    StringBuffer tzname(tz.getName());
    LOG.debug("EventDataStore: getTimezoneName %s", tzname.c_str());
    return tzname;
}

StringBuffer EventDataStore::getBooleanValue(bool val) {    
    return val == true ? "1" : "0";
}

StringBuffer EventDataStore::createInsertEventStmt(Event& event) {    
     
    StringBuffer query("INSERT INTO ");
    query.append(TABLE_EVENT);
    query.append(" (");    
    // list of fields
    query.append("summary, location, description,");
    query.append("start_date, start_tz, end_date,");
    query.append("all_day, calendar_id, orig_event_id, orig_start_date");
    
    // new for 2.0 OS
    query.append(", organizer_id, organizer_is_self, status, external_status");
        
    // list of values
    query.append(") values (");    
    
    StringBuffer toEsc(event.getSubject());
    escapesQuery(toEsc);
    query.append("'").append(toEsc).append("'").append(",");
    toEsc = event.getLocation();
    escapesQuery(toEsc);    
    query.append("'").append(toEsc).append("'").append(",");
    toEsc = event.getBody();
    escapesQuery(toEsc);
    query.append("'").append(toEsc).append("'").append(",");
            
    query.append(getDate(event.getStart())).append(",");
    query.append("'").append(getTimezoneName(event.getEventTimezone())).append("'").append(",");
    
    Date& dtmp = event.getEnd();
    normalizeForAllDay(dtmp, event.getAllDayEvent());
    query.append(getDate(dtmp)).append(",");
        
    query.append(getBooleanValue(event.getAllDayEvent())).append(",");    
    query.append(event.getCalendar()).append(",");
    query.append(event.getorig_event_id()).append(",");
    query.append(event.getorig_start_date());
    
    // for 2.0 OS
    query.append(", 0, 0, 0, 0");
          
    query.append(");");
    
    LOG.debug("**EventDataStore:createInsertEventStmt: %s\n", query.c_str()); 
    return query;          
}

StringBuffer EventDataStore::createInsertEventChangesStmt(Event& event, const char* identifier) { 
    
    StringBuffer query("INSERT INTO ");
    query.append(TABLE_EVENT_CHANGES);
    query.append(" ("); 
    // list of fields
    query.append("record, type, calendar_id, external_id, store_id");
    
    // list of values
    query.append(") values ("); 
    
    query.append(identifier).append(",");
    query.append("0").append(",");
    query.append(event.getCalendar()).append(",");
    query.append("NULL").append(",");
    query.append("1");
    query.append(");");
    LOG.debug("**EventDataStore:createInsertEventChangesStmt: %s\n", query.c_str());
    return query;
}

StringBuffer EventDataStore::createInsertCalendarChangesStmt(Event& event) { 
    
    StringBuffer query("INSERT INTO ");
    query.append(TABLE_CALENDAR_CHANGES);
    query.append(" (");
    // list of fields
    query.append("record, type, store_id, external_id");
    
    // list of values
    query.append(") values (");
    
    query.append(event.getCalendar()).append(",");
    query.append("1").append(",");
    query.append("1").append(",");
    query.append("NULL");
    query.append(");");
    
    LOG.debug("**EventDataStore:createInsertCalendarChangesStmt: %s\n", query.c_str());
    return query;
}

StringBuffer EventDataStore::createInsertOccurrenceCacheStmt(Event& event, const char* identifier) {    
    
    Date& start = event.getStart();
       
    StringBuffer startOccurrence(getDate(start));
    StringBuffer day(getLocalMidnight(start));
    
    
    StringBuffer query("INSERT INTO ");
    query.append(TABLE_OCCURRENCE_CACHE);
    query.append(" (");    
    // list of fields
    query.append("day, event_id, calendar_id, store_id, occurrence_date");
    
    // list of values
    query.append(") values (");    
   
    query.append(day).append(",");    
    query.append(identifier).append(",");
    query.append(event.getCalendar()).append(",");
    query.append("1").append(",");    
    query.append(startOccurrence);    
    query.append(");");
   
    LOG.debug("**EventDataStore:createInsertOccurrenceCacheStmt: %s\n", query.c_str()); 
    return query;          
}


/*************** END CREATION EVENT **************/

StringBuffer EventDataStore::createUpdateEventStmt(const char* identifier, Event& event) {    
    
    StringBuffer toEsc;
    StringBuffer query("UPDATE ");
    query.append(TABLE_EVENT);
    query.append(" set ");    
    // list of fields
    query.append("summary = "); 
    toEsc = event.getSubject();
    escapesQuery(toEsc);
    query.append("'").append(toEsc).append("'").append(",");
    
    query.append("location = "); 
    toEsc = event.getLocation();
    escapesQuery(toEsc);
    query.append("'").append(toEsc).append("'").append(",");
    
    query.append("description = ");
    toEsc = event.getBody();
    escapesQuery(toEsc);
    query.append("'").append(toEsc).append("'").append(",");
    
    query.append("start_date = "); 
    query.append(getDate(event.getStart())).append(",");
    
    query.append("start_tz = "); 
    query.append("'").append(getTimezoneName(event.getEventTimezone())).append("'").append(",");
    
    query.append("end_date = "); 
    query.append(getDate(event.getEnd())).append(",");
    
    query.append("all_day = "); 
    query.append(getBooleanValue(event.getAllDayEvent())).append(",");  
    
    query.append("calendar_id = "); 
    query.append(event.getCalendar()).append(","); 
    
    query.append("orig_event_id = "); 
    query.append(event.getorig_event_id()).append(",");
    
    query.append("orig_start_date = "); 
    query.append(event.getorig_start_date());
      
    query.append(" where ROWID = ");
    query.append(identifier);    
    query.append(";");    
    LOG.debug("**EventDataStore:createUpdateEventStmt: %s\n", query.c_str());     
    return query;          
}

/**
 * Create the query to udpate the OccurrenceCache for the single event.
 */
StringBuffer EventDataStore::createUpdateSingleOccurrenceCacheStmt(Event& event, const char* identifier) {    
    
    Date& d = event.getStart(); 
    StringBuffer day(d.getDate());
    if (day.length() > 8) {
        StringBuffer tmp = day.substr(0, 8);
        day = tmp;
        day.append("T0000");  
    }   
    
    StringBuffer query("UPDATE ");
    query.append(TABLE_OCCURRENCE_CACHE);
    query.append(" set ");    
    
    // list of values
    Date dd(day); 
    query.append(" day = ");
    query.append(getDate(dd)).append(",");    
    
    query.append(" calendar_id = ");
    query.append(event.getCalendar()).append(",");
    
    query.append(" occurrence_date = ");
    query.append(getDate(event.getStart()));   
    
    query.append(" where event_id = ");
    query.append(identifier);    
    query.append(";");    
    
    LOG.debug("**EventDataStore:createInsertOccurrenceCacheStmt: %s\n", query.c_str()); 
    return query;          
}

StringBuffer EventDataStore::createUpdateEventChangesStmt(const char* identifier, Event& event) {
    
    StringBuffer query("INSERT INTO ");
    query.append(TABLE_EVENT_CHANGES);
    query.append(" (");
    // list of fields
    query.append("record, type, calendar_id, external_id, store_id");
    
    // list of values
    query.append(") values (");
    
    query.append(identifier).append(",");
    query.append("1").append(",");
    query.append(event.getCalendar()).append(",");
    query.append("NULL").append(",");
    query.append("1");
    query.append(");");
    LOG.debug("**EventDataStore:createUpdateEventChangesStmt: %s\n", query.c_str());
    return query;
}


//***********************************+
// ********* DELETE QUERIES **********

StringBuffer EventDataStore::createDeleteEventStmt(const char* id) {
    
    StringBuffer query("DELETE FROM ");
    query.append(TABLE_EVENT);
    query.append(" WHERE ROWID = ");
    query.append(id);
    query.append(";");
    LOG.debug("**EventDataStore:createDeleteEventStmt: %s\n", query.c_str()); 
    return query;
    
}

StringBuffer EventDataStore::createDeleteEventChangesStmt(Event& event, const char* identifier) {
    
    StringBuffer query("INSERT INTO ");
    query.append(TABLE_EVENT_CHANGES);
    query.append(" (");
    // list of fields
    query.append("record, type, calendar_id, external_id, store_id");
    
    // list of values
    query.append(") values (");
    
    query.append(identifier).append(",");
    query.append("2").append(",");
    query.append(event.getCalendar()).append(",");
    query.append("NULL").append(",");
    query.append("1");
    query.append(");");
    LOG.debug("**EventDataStore:createDeleteEventChangesStmt: %s\n", query.c_str());
    return query;
}

StringBuffer EventDataStore::createDeleteOccurrenceCacheStmt(const char* identifier) {
    
    if (!identifier) {
        LOG.debug("EventDataStore:createDeleteOccurrenceCacheStmt: the identifier is NULL");
        return "";
    }
    StringBuffer query("DELETE FROM ");
    query.append(TABLE_OCCURRENCE_CACHE);
    query.append(" WHERE event_id = ");
    query.append(identifier);
    query.append(";");
    LOG.debug("**EventDataStore:createDeleteOccurrenceCacheStmt: %s\n", query.c_str()); 
    return query;
    
}

// ***** end delete queries *********    

/**
 * Update the fun table that takes trace of the changes 
 */
StringBuffer EventDataStore::createUpdateFunEventStateStmt(const char* id) {
    
    StringBuffer query("Update fun_event_state ");
    query.append("set updated_from_client = 1 ");
    query.append("where event_id = ");
    query.append(id);
    query.append(";");
    LOG.debug("**EventDataStore:createUpdateFunEventStateStmt: %s\n", query.c_str());
    return query;

}

int EventDataStore::handleAlarm(Event& event, const char* identifier, int action) {
    
    int ret = 0;
    ArrayList al;
    
    if (event.getReminderSet()) {
        if (action == 0) { // the item is going to be added
			LOG.debug("Inserting 1 Alarm");
            ret = alarmds.insertAlarm(event, identifier);
            if (ret != 0) {
                LOG.debug("EventDataStore.handleAlarm: Problem inserting the alarm: ret = %i", ret);
            }
        } else if (action == 1) { // the item is going to be updated
            if (alarmds.containsReminder(identifier, al)) {
                
                bool foundEquals = false;
                Alarm* alarm = event.getAlarm(); // alarm contains the values in long format
                ArrayList times;
                int index = 0;
                for (int i = 0; i < al.size(); i++) {
                    
                    if (foundEquals) {
                        break;
                    }
                    StringBuffer query("select trigger_interval from Alarm where rowid = ");
                    query.append((*(StringBuffer*)al.get(i))).append(";");
                    LOG.debug("EventDataStore.handleAlarm: query is %s", query.c_str());
                    ret = driver.queryIndex(query, times);
                    
                    if (times.size() > 0) {
                        StringBuffer* t = (StringBuffer*)times.get(0);
                        if ((strcmp(t->c_str(), alarm->getPrimary_secBeforeStart(NULL).c_str()) == 0) ||
                            (strcmp(t->c_str(), alarm->getSecondary_secBeforeStart(NULL).c_str()) == 0) ) {
                                // there is a reminder that is the same, so nothing update
                            foundEquals = true;
                            index = i;
                            break; // the i index is the one of the right alarm rowid
                        } 
                    }                    
                }
                if (!foundEquals && al.size() > 0) { // they are not equal. We update the first one
                    ret = alarmds.updateAlarm(event, identifier, ((StringBuffer*)al.get(0))->c_str());
					if (ret != 0) {
                        LOG.debug("EventDataStore.handleAlarm: Problem udpating the alarm: ret = %i", ret);
                    }
					LOG.debug("Updated 1 Alarm");
                    
					if(al.size() > 1) {
						ret = alarmds.updateAlarm(event, identifier, ((StringBuffer*)al.get(1))->c_str());
						if (ret != 0) {
							LOG.debug("EventDataStore.handleAlarm: Problem udpating the alarm: ret = %i", ret);
						}
						LOG.debug("Updated 2 Alarm");
					}
                }
            } else {
				LOG.debug("No reminder, inserting Alarm");
                ret = alarmds.insertAlarm(event, identifier);
                if (ret != 0) {
                    LOG.debug("EventDataStore.handleAlarm: Problem inserting the alarm: ret = %i", ret);
                }
            }
            
        } else if (action == 2) { // the item is going to be deleted
			LOG.debug("Deleting Alarm");
            ret = alarmds.deleteAlarm(event, identifier, false);
            if (ret != 0) {
                LOG.debug("EventDataStore.handleAlarm: Error after deleteAlarm call: ret = %i", ret);
            }            
        }

    } else {
        if (action == 0) { // the item is going to be added
            LOG.debug("EventDataStore.handleAlarm: Nothing to do: the adding event doesn't contain an alarm");
        } else if (action == 1) { // the item is going to be updated and the alarm is removed.
            if (alarmds.containsReminder(identifier, al)) {
                // delete the alarm and update the alarmChanges table
				LOG.debug("Deleting Alarm, no reminder");
                ret = alarmds.deleteAlarm(event, identifier, true);
                if (ret != 0) {
                    LOG.debug("EventDataStore.handleAlarm: Error after updateAlarm call: ret = %i", ret);
                }
            }
        } else if (action == 2) { // the item is going to be deleted
			LOG.debug("Deleting Alarm action2");
            ret = alarmds.deleteAlarm(event, identifier, false);
            if (ret != 0) {
                LOG.debug("EventDataStore.handleAlarm: Error after deleteAlarm call: ret = %i", ret);
            }
        }
        
    }
    
    return ret;
    
} 

void EventDataStore::setValidCalendarId(Event& event) {
 
    ArrayList al;
    StringBuffer* cal_id = NULL;
    int ret = driver.queryIndex(QUERY_MAX_ID_FROM_CALENDAR, al);
    LOG.debug("The existing calendar that are in the table calendar: %s", QUERY_MAX_ID_FROM_CALENDAR);
    if (ret != 0){
        LOG.error("EventDataStore.setValidCalendarId: error queryIndex: err = %i", ret);                
        return;
    }
    cal_id = (StringBuffer*)al.get(0);
    event.setCalendar(cal_id->c_str());
    return;
}

void EventDataStore::getPreviousCalendarId(Event& event, const char* identifier) {
    
    ArrayList al;
    StringBuffer* cal_id= NULL;
    StringBuffer query = "Select calendar_id from event where rowid = ";
    query.append(identifier).append(";");
    LOG.debug("The getPreviousCalendarId query: %s", query.c_str());
    int ret = driver.queryIndex(query, al);
    if (ret != 0){
        LOG.error("EventDataStore.getPreviousCalendarId: error queryIndex: err = %i", ret);                
        return;
    }
    cal_id = (StringBuffer*)al.get(0);
    event.setCalendar(cal_id->c_str());
    return;
}

int EventDataStore::addPIMItem(StringBuffer& identifier, const PIMItem& item){

    int ret = 0;    
    Event& event = (Event&)item;
    ArrayList al;
    
    // to be sure to set a valid calendar_id
    setValidCalendarId(event);
    
    StringBuffer query = createInsertEventStmt(event);
    
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: the item %s can not be inserted: err = %i", event.getSubject(), ret);        
        goto finally;
        
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: error commit: err = %i", ret);                
        goto finally;
    }

    ret = driver.queryIndex(QUERY_MAX_ID_FROM_EVENT, al);
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: error queryIndex: err = %i", ret);                
        goto finally;        
    }
    identifier = ((StringBuffer*)(al.get(0)))->c_str();
    
    // EventChanges table
    query = createInsertEventChangesStmt(event, identifier);
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: error createInsertEventChangesStmt: err = %i", ret);
        goto finally;
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: error commit: err = %i", ret);
        goto finally;
    }
    // CalendarChanges table
    query = createInsertCalendarChangesStmt(event);
    ret = driver.execute(query);
    if (ret != 0) {
        LOG.error("EventDataStore.addPIMItem: error createInsertCalendarChangesStmt: err = %i", ret);
        goto finally;
    }
    ret = driver.commit();
    if (ret != 0) {
        LOG.error("EventDataStore.addPIMItem: error commit: err = %i", ret);
        goto finally;
    }
    
    // **** Alarm handling ****
    ret = handleAlarm(event, identifier, 0);
    if (ret != 0) {
        LOG.error("EventDataStore.addPIMItem: error in handling Alarm: err = %i", ret);
        goto finally;
    }
    
    if (event.getIsRecurring() == false) {    
        // OccurrenceCaches
        query = createInsertOccurrenceCacheStmt(event, identifier);
        ret = driver.execute(query);    
        if (ret != 0){
            LOG.error("EventDataStore.addPIMItem: error createInsertOccurrenceCacheStmt: err = %i", ret);                
            goto finally;
        }    
        ret = driver.commit();
        if (ret != 0){
            LOG.error("EventDataStore.addPIMItem: error commit: err = %i", ret);
            goto finally;
        }
    }
    else {
        ret = recurrenceds.insertRecurrence(event, identifier.c_str());
        if (ret != 0){
            LOG.error("EventDataStore.insertRecurrence: insertRecurrence error: err = %i", ret);
            goto finally;
        }
    }
    
    query = createUpdateFunEventStateStmt(identifier);
    ret = driver.execute(query);    
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: error createUpdateFunEventStateStmt: err = %i", ret);                
        goto finally;
    }
    
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.addPIMItem: error commit after fun_event_state: err = %i", ret);                
        goto finally;
    }
    
finally:
    return ret;
}

int EventDataStore::deletePIMItem(const char* identifier) {

    int ret = 0;
    ArrayList al;
    StringBuffer query;
    Event* event = (Event*)getPIMItem(identifier);
    if (!event) {
        LOG.debug("EventDataStore:createDeleteEventChangesStmt: the Event retrieved is NULL");
        ret = 1;
        goto finally;
    }
    
    // set the previous calendar id
    getPreviousCalendarId(*event, identifier);
    
    query = createDeleteEventChangesStmt(*event, identifier);
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error queryIndex: err = %i", ret);                
        goto finally;            
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error commit after createDeleteEventChangesStmt: err = %i", ret);
        goto finally;
    }
    
    // **** Alarm handling ****
    ret = handleAlarm(*event, identifier, 2);
    if (ret != 0) {
        LOG.error("EventDataStore.addPIMItem: error in handling Alarm: err = %i", ret);
        goto finally;
    }
    
    query = createDeleteEventStmt(identifier);
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error createDeleteEventStmt: err = %i", ret);
        goto finally;
    }
    
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error commit after createDeleteEventStmt: err = %i", ret);
        goto finally;
    }
    
    query = createDeleteOccurrenceCacheStmt(identifier);
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.createDeleteOccurrenceCacheStmt: error createDeleteOccurrenceCacheStmt: err = %i", ret);
        goto finally;
    }
    
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error commit after createDeleteOccurrenceCacheStmt: err = %i", ret);
        goto finally;
    }
//
// for the recurrence
//    
/*     
    // is that event recurrence?
    query = QUERY_IF_RECURRENCE;
    query.append(identifier).append(";");
    
    ret = driver.queryIndex(query, al);
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: query for recurrence: err = %i", ret);                
        goto finally;
    }
    
    if (al.size() > 0) {
        // the event is recurrent
        rds.deleteRecurrence(identifier);
    }
*/    
    query = createUpdateFunEventStateStmt(identifier);
    ret = driver.execute(query);    
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error createUpdateFunEventStateStmt: err = %i", ret);                
        goto finally;
    }
    
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.deletePIMItem: error commit after fun_event_state: err = %i", ret);                
        goto finally;
    }
    
finally:    
    return ret;
    
}

int EventDataStore::updatePIMItem(const char* identifier, const PIMItem& item) {
    
    int ret = 0;    
    Event& event = (Event&)item;
    ArrayList al;
    
    // set the previous calendar id
    getPreviousCalendarId(event, identifier);

    StringBuffer query = createUpdateEventStmt(identifier, event);
    
    ret = driver.execute(query);    
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem: the item %s can not be updated: err = %i", event.getSubject(), ret);
        goto finally;
        
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem: error commit: err = %i", ret);                
        goto finally;
    }        
    
    query = createUpdateEventChangesStmt(identifier, event);
    ret = driver.execute(query);    
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem, createUpdateEventChangesStmt: the item %s can not be updated: err = %i", event.getSubject(), ret);
        goto finally;
        
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem: error commit: err = %i", ret);                
        goto finally;
    }        
    
    query = createUpdateSingleOccurrenceCacheStmt(event, identifier);
    ret = driver.execute(query);    
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem, createUpdateSingleOccurrenceCacheStmt: the item %s can not be updated: err = %i", event.getSubject(), ret);
        goto finally;
        
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem: error commit: err = %i", ret);                
        goto finally;
    }        	   
    
    // Handle Alarm tables
    ret = handleAlarm(event, identifier, 1);
    
    query = createUpdateFunEventStateStmt(identifier);
    ret = driver.execute(query);    
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem: error createUpdateFunEventStateStmt: err = %i", ret);                
        goto finally;
    }
    
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.updatePIMItem: error commit after fun_event_state: err = %i", ret);                
        goto finally;
    }
    
    // @todo: handle the Recurrence Object if any...
finally:
       
    return ret;
}

PIMItem* EventDataStore::getPIMItem(const char* identifier) { 

    // testTimezoneInformation();
    
    Event* event = NULL;        
    Alarm* alarm = NULL;
    Recurrence* recurrence = NULL;
    
    StringBuffer query = "SELECT summary, location, description, start_date, start_tz, end_date, \
                          all_day, calendar_id, orig_event_id, orig_start_date FROM Event WHERE ROWID = ";
    query.append(identifier);
    query.append(";");
    ArrayList al;
    int ret = driver.queryColumns(query, al);
    if (ret != 0) {
        LOG.error("getPIMItem error to get the item with id = %s", identifier);
        goto finally;
    }
    event = populateEvent(al);
    if (event == NULL) {
        LOG.error("getPIMItem error to populate the item with id = %s", identifier);   
        goto finally;
    }
    al.clear();
    
    alarm = alarmds.getAlarm(*event, identifier);
    if (alarm) {
        event->setAlarm(alarm);
        event->setReminderSet(true);
    } else {
        event->setReminderSet(false);
        LOG.debug("EventDataStore.getPIMItem: the event doesn't contain alarms");
    }
        
    // handle the Recurrence that has to be retrieved from the tables
    recurrence = recurrenceds.getRecurrence(*event, identifier);
    if (recurrence) {
        event->setRecurrence(recurrence);
        event->setIsRecurring(true);
    } else {
        LOG.debug("EventDataStore.getPIMItem: the event doesn't contain recurrence");
    }
    
finally:
    return event; 
}

/**
* this is the order to get the fields
  "SELECT summary, location, description, start_date, start_tz, end_date, \
   all_day, calendar_id, orig_event_id, orig_start_date FROM Event WHERE ROWID = ";
*/                         
Event* EventDataStore::populateEvent(const ArrayList& al) {

    int size = al.size();
	LOG.debug("The size of the arrayList in populateEvent Event %i", size);
    if (size == 0) {
        return NULL;
    }
    
    Event* event = new Event();
    Timezone tz; 
    bool isAllDay = false;
    
    if (size > 6) {
        isAllDay = convertStringToBool(getElementOfArray(al, 6));        
    }
    if (size > 4) {
        tz.setName(getElementOfArray(al, 4));                
    }
        
    for (int i = 0; i < size; i++) {
        
        switch(i) {
            case 0: {
                event->setSubject(getElementOfArray(al, 0));
                break;
            }
            case 1: {
                event->setLocation(getElementOfArray(al, 1));
                break;
            }
            case 2: {
                event->setBody(getElementOfArray(al, 2));
                break;
                }
            case 3: {
                StringBuffer s = convertAppleDateToStandard(getElementOfArray(al, 3), isAllDay);
                Date start(s.c_str());
                event->setStart(start);
                break;
                }
            case 4: {
                event->setEventTimezone(tz);
                break;
                }
            case 5: {
                StringBuffer s = convertAppleDateToStandard(getElementOfArray(al, 5), isAllDay);
                Date end(s.c_str());
                event->setEnd(end);
                break;
                }
            case 6: {
                event->setAllDayEvent(isAllDay);
                break;
                }
            case 7: {
                event->setCalendar(getElementOfArray(al, 7));                
                break;
                }
            case 8: {
                event->setorig_event_id(getElementOfArray(al, 8));                
                break;
                }
            case 9: {
                event->setorig_start_date(getElementOfArray(al, 9));                
                break;            
                }
            default:
                LOG.error("Property not supported");
                break;
            }                    
    }
        
    return event;    
}

int EventDataStore::resetFlags(unsigned long lastsync) {

    StringBuffer query;
    query.sprintf("UPDATE fun_event_state SET creation = %li WHERE creation >= %li AND updated_from_client = 1 ;", lastsync, lastsync);
    LOG.debug("EventDataStore resetFlags: %s", query.c_str());
    int ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in EventDataStore");
        return ret;
    }
    ret = driver.commit();

    query.sprintf("UPDATE fun_event_state SET deleted = %li WHERE deleted >= %li AND updated_from_client = 1 ;", lastsync, lastsync);
    LOG.debug("EventDataStore resetFlags: %s", query.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in EventDataStore");
        return ret;
    }
    ret = driver.commit();

    query.sprintf("UPDATE fun_event_state SET updated = %li WHERE updated >= %li AND updated_from_client = 1 ;", lastsync, lastsync);
    LOG.debug("EventDataStore resetFlags: %s", query.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in EventDataStore");
        return ret;
    }
    ret = driver.commit();


    query ="UPDATE fun_event_state SET updated_from_client = 0;";
    LOG.debug("EventDataStore resetFlags: %s", query.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in NoteDataStore");
        return ret;
    }
    ret = driver.commit();
    
    return ret;
    
}

int EventDataStore::removeAllItems() {
    
    int ret = 0;
    ArrayList al;
    StringBuffer query("delete from Event;");
       
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.removeAllItems: error execute %s: err = %i", query.c_str(), ret);                
        goto finally;            
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.removeAllItems: error commit after %s: err = %i", query.c_str(), ret);
        goto finally;
    }
    
    query = "delete from fun_event_state";
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("EventDataStore.removeAllItems: error execute %s: err = %i", query.c_str(), ret);                
        goto finally;            
    }
    ret = driver.commit();
    if (ret != 0){
        LOG.error("EventDataStore.removeAllItems: error commit after %s: err = %i", query.c_str(), ret);
        goto finally;
    }
    
    
finally:    
    return ret;
    
}
